package mysql

import (
	"database/sql"
	"fmt"
	_ "github.com/go-sql-driver/mysql"
	"log"
)

/*
数据库连接设置 autocommit = 0 ,代码设置提交事务,无效
*/
func main() {
	var isInsert bool = false
	db, _ := sql.Open("mysql", "root:root@tcp(localhost:3306)/test?charset=utf8mb4&autocommit=1&parseTime=true")

	error := db.Ping()
	if error != nil {
		log.Println(error)
	}

	var username string
	var password string

	fmt.Println("/* 1) 一般查询 */")
	//rows ,err :=db.Query("select username,password from users where id = ?",1)
	rows, err := db.Query("select username,password from users ")
	if err != nil {
		fmt.Println(err)
	}
	defer rows.Close()
	for rows.Next() {
		err := rows.Scan(&username, &password)
		if err != nil {
			fmt.Println(err)
		}
		fmt.Println("username:", username, "password:", password)
	}
	err = rows.Err()
	if err != nil {
		fmt.Println(err)
	}

	fmt.Println("/* 2) 单条查询 */")
	err = db.QueryRow("select * from users where id =?", 1).Scan(&username, &password)
	if err != nil {
		fmt.Println("username:", username, "password:", password)
	}

	if isInsert {
		fmt.Println("/*插入*/")

		tx, err := db.Begin()
		if err != nil {
			return
		}
		// 插入数据入表
		stmt, err := db.Prepare("insert into users(username, password) values (?,?)")
		if err != nil {
			log.Println(err)
		}
		rs, err := stmt.Exec("go-test", "123456")
		if err != nil {
			log.Println(err)
		}

		//我们可以获得插入的id
		id, err := rs.LastInsertId()
		if err != nil {
			fmt.Println("exec failed,", err)
			// 回滚
			tx.Rollback()
			return
		}

		//可以获得影响行数
		affect, err := rs.RowsAffected()

		fmt.Printf("插入数据:id:%d,affect:%d", id, affect)
		tx.Commit()

	}

	defer db.Close()
}
